package test.dbunit;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import test.com.dcivision.framework.DatabaseManage;
import test.com.dcivision.framework.DcivisionDataSource;

import com.dcivision.framework.SqlAccountUtility;
import com.dcivision.framework.Utility;

/**
 * 
 */

/**
 * @author Administrator
 */
public class Alert317_320 {
  boolean isConversionForm     = true;

  boolean isConversionEveryone = true;

  public static void main(String[] args) throws SQLException {
    Alert317_320 alert=new Alert317_320();
    alert.run();
  }
  
  public void insertStepFormRecord(Connection conn, int ID, int workflowStepID, int formRecordID, int formPageID, int formSectionID, int formElementID,
      String permission, int creatorID, String createDate, int updaterID, String updateDate

  ) {
    java.sql.PreparedStatement stat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    sqlStat.append(" INSERT INTO   MTM_WF_STEP_FORM_RECORD ");
    sqlStat
        .append(" (ID, WORKFLOW_STEP_ID, FORM_RECORD_ID, FORM_PAGE_ID, FORM_SECTION_ID, FORM_ELEMENT_ID, PERMISSION, MANDATORY, RECORD_STATUS, UPDATE_COUNT, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE) ");
    sqlStat.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");

    try {
      stat = conn.prepareStatement(sqlStat.toString());
      stat.setInt(1, ID);
      stat.setInt(2, workflowStepID);
      stat.setInt(3, formRecordID);
      if (formPageID == -1) {
        stat.setNull(4, java.sql.Types.VARCHAR);
      } else {
        stat.setInt(4, formPageID);
      }
      if (formSectionID == -1) {
        stat.setNull(5, java.sql.Types.VARCHAR);
      } else {
        stat.setInt(5, formSectionID);
      }
      if (formElementID == -1) {
        stat.setNull(6, java.sql.Types.VARCHAR);
      } else {
        stat.setInt(6, formElementID);
      }

      stat.setString(7, permission);
      stat.setString(8, "Y");
      stat.setString(9, "A");
      stat.setInt(10, 1);
      stat.setInt(11, creatorID);
      stat.setString(12, createDate);
      stat.setInt(13, updaterID);
      stat.setString(14, updateDate);

      rs = stat.executeQuery();
      System.out.println("## Inser succeed ##<br>");
      rs.close();
      stat.close();
    } catch (Exception ex) {
      System.out.println(ex.toString());
      rs = null;
      stat = null;
    }
  }

  public void insertRecord(Connection conn, String formSectionIDStr, int stepID, int ID, String permission, int creatorID, String createDate, int updaterID,
      String updateDate) {

    //fixme for element insert
    Statement stat3 = null;
    ResultSet rs3 = null;
    StringBuffer sqlStat3 = new StringBuffer();
    sqlStat3.append(" SELECT A.ID AS FORM_RECORD_ID, B.ID AS FORM_PAGE_ID, C.ID AS FORM_SECTION_ID, ");
    sqlStat3.append(" C.SUB_SECTION ");

    sqlStat3.append(" FROM FORM_RECORD A, FORM_PAGE B, FORM_SECTION C, MTM_FORM_PAGE_FORM_SECTION D ");
    sqlStat3.append(" WHERE A.ID = B.FORM_RECORD_ID ");
    sqlStat3.append(" AND B.ID = D.PAGE_ID ");
    sqlStat3.append(" AND D.SECTION_ID = C.ID ");
    sqlStat3.append(" AND A.RECORD_STATUS = B.RECORD_STATUS ");
    sqlStat3.append(" AND A.RECORD_STATUS = C.RECORD_STATUS ");
    sqlStat3.append(" AND A.RECORD_STATUS = D.RECORD_STATUS ");
    // sqlStat3.append(" AND C.SUB_SECTION = 'Y' ");
    sqlStat3.append(" AND C.ID = " + formSectionIDStr);

    try {
      int formRecordID = -1;
      int formPageID = -1;
      int formSectionID = -1;

      stat3 = conn.createStatement();
      rs3 = stat3.executeQuery(sqlStat3.toString());

      if (rs3.next()) {
        System.out.println("F:P:S::  " + rs3.getInt("FORM_RECORD_ID") + "    : " + rs3.getInt("FORM_PAGE_ID") + "    : " + rs3.getString("FORM_SECTION_ID")
            + "<br>");
        formRecordID = rs3.getInt("FORM_RECORD_ID");
        formPageID = rs3.getInt("FORM_PAGE_ID");
        formSectionID = rs3.getInt("FORM_SECTION_ID");

        System.out.println("####################### Insert Mtm_WF_Step_Form_Record Table #####################<br>");
        insertStepFormRecord(conn, ID, stepID, formRecordID, formPageID, formSectionID, -1, permission, creatorID, createDate, updaterID, updateDate);

      }
      rs3.close();
      stat3.close();
    } catch (Exception ex) {
      rs3 = null;
      stat3 = null;
      System.out.println(ex.toString());
    }
  }

  public void insertUserActor(Connection conn, int ID, int workflowStepID, String actionType, int creatorID, String createDate, int updaterID, String updateDate

  ) {
    java.sql.PreparedStatement stat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    sqlStat.append(" INSERT INTO   MTM_WF_STEP_USER_ACTOR ");
    sqlStat
        .append(" (ID, WORKFLOW_STEP_ID, ACTOR_TYPE, ACTOR_ID, PERMISSION, ACTION_TYPE, RECORD_STATUS, UPDATE_COUNT, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE) ");
    sqlStat.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");

    try {
      stat = conn.prepareStatement(sqlStat.toString());
      stat.setInt(1, ID);
      stat.setInt(2, workflowStepID);
      stat.setString(3, "Z");
      stat.setInt(4, 0);
      stat.setString(5, "R");
      stat.setString(6, actionType);
      stat.setString(7, "A");
      stat.setInt(8, 1);
      stat.setInt(9, creatorID);
      stat.setString(10, createDate);
      stat.setInt(11, updaterID);
      stat.setString(12, updateDate);

      rs = stat.executeQuery();
      System.out.println("## Inser succeed ##<br>");
      rs.close();
      stat.close();
    } catch (Exception ex) {
      System.out.println(ex.toString());
      rs = null;
      stat = null;
    }
  }

  // Update Form_Submission Table
  public void updateFormSubmission(Connection conn, int ID, int workflowRecordID, int trackID) {
    java.sql.PreparedStatement stat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    sqlStat.append(" UPDATE FORM_SUBMISSION ");
    sqlStat.append(" SET WORKFLOW_RECORD_ID=?, TRACK_ID=? ");
    sqlStat.append(" WHERE ID=? ");

    try {
      stat = conn.prepareStatement(sqlStat.toString());
      stat.setInt(1, workflowRecordID);
      stat.setInt(2, trackID);
      stat.setInt(3, ID);

      rs = stat.executeQuery();
      System.out.println("## Update Form_Submission succeed ##<br>");
      rs.close();
      stat.close();
    } catch (Exception ex) {
      System.out.println(ex.toString());
      rs = null;
      stat = null;
    }
  }

  public void run() throws SQLException {
    Connection conn = null;
    try {
      conn = DatabaseManage.getCurrentConnection();
      isConversionForm = isConversionForm(conn);
      
      insertFormElementWithWorkflowStep(conn);

      //if current Step not end step,insert everyone read permission on the table MTM_WF_STEP_USER_ACTOR
      insertEveryoneRPermissonForNotExecuteAction(conn);

      convertFormSubmission(conn);
      
      //fixme AutoTask DB

     // conn.commit();
    } catch (Exception ex) {
      System.out.println(ex.toString());
    } finally {
      conn.close();
    }
    
    
    
    // autotask
    
  }

  /**
   * @param conn
   * @return
   * @throws SQLException
   */
  private Statement insertFormElementWithWorkflowStep(Connection conn) throws SQLException {
    Statement stat;
    ResultSet rs = null;
    int ID = 0;
    StringBuffer sqlStat1 = new StringBuffer();
    sqlStat1.append(" SELECT  ");
    sqlStat1.append(" A.ID AS FORM_RECORD_ID, D.ID AS PAGE_ID, E.SECTION_ID, F.SUB_SECTION,  ");
    sqlStat1.append(" B.WORKFLOW_RECORD_ID, C.ID AS STEP_ID, C.STEP_SEQ, C.RECORD_STATUS,  ");
    sqlStat1.append(" C.CREATOR_ID, C.CREATE_DATE, C.UPDATER_ID, C.UPDATE_DATE ");
    sqlStat1.append(" FROM FORM_RECORD A, MTM_WF_RECORD_OBJECT_LINK B, WORKFLOW_STEP C, ");
    sqlStat1.append("      FORM_PAGE D, MTM_FORM_PAGE_FORM_SECTION E, FORM_SECTION F ");
    sqlStat1.append(" WHERE A.ID = B.OBJECT_ID ");
    sqlStat1.append(" AND B.WORKFLOW_RECORD_ID = C.WORKFLOW_RECORD_ID ");
    sqlStat1.append(" AND A.ID = D.FORM_RECORD_ID ");
    sqlStat1.append(" AND D.ID = E.PAGE_ID ");
    sqlStat1.append(" AND E.SECTION_ID = F.ID ");
    sqlStat1.append(" AND A.RECORD_STATUS = B.RECORD_STATUS ");
    sqlStat1.append(" AND A.RECORD_STATUS = C.RECORD_STATUS ");
    sqlStat1.append(" AND A.RECORD_STATUS = D.RECORD_STATUS ");
    sqlStat1.append(" AND A.RECORD_STATUS = E.RECORD_STATUS ");
    sqlStat1.append(" AND A.RECORD_STATUS = F.RECORD_STATUS ");
    sqlStat1.append(" AND B.OBJECT_TYPE ='M' ");
    sqlStat1.append(" AND A.RECORD_STATUS = 'A' ");
    sqlStat1.append(" AND C.STEP_SEQ = 1 ");
    sqlStat1.append(" AND F.SUB_SECTION = 'N' ");
    sqlStat1.append(" ORDER BY A.ID ");

    stat = conn.createStatement();
    if (isConversionForm) {
      rs = stat.executeQuery(sqlStat1.toString());
    }
    while (!Utility.isEmpty(rs) && rs.next()) {
      ID++;
      // out.println(rs.getInt("FORM_RECORD_ID") +
      // " : "+rs.getInt("PAGE_ID") +
      // " : "+rs.getInt("SECTION_ID") +
      // " : "+rs.getInt("WORKFLOW_RECORD_ID") +
      // " : "+rs.getInt("STEP_ID") +
      // " : "+rs.getInt("CREATOR_ID") +
      // " : "+rs.getString("CREATE_DATE") +
      // " : "+rs.getInt("UPDATER_ID") +
      // " : "+rs.getString("UPDATE_DATE") + "<br>");

      // out.println("####################### Insert Mtm_WF_Step_Form_Record
      // Table #####################<br>");

      insertStepFormRecord(conn, ID, rs.getInt("STEP_ID"), rs.getInt("FORM_RECORD_ID"), rs.getInt("PAGE_ID"), rs.getInt("SECTION_ID"), -1, "C", rs
          .getInt("CREATOR_ID"), rs.getString("CREATE_DATE"), rs.getInt("UPDATER_ID"), rs.getString("UPDATE_DATE"));
    }
    // out.println("####################### Get All Binding Section
    // #####################<br>");
    StringBuffer sqlStat2 = new StringBuffer();
    sqlStat2.append(" SELECT A.ID, A.FORM_SECTION_ID, ");
    sqlStat2.append(" A.RECORD_STATUS,  ");
    sqlStat2.append(" A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
    sqlStat2.append(" FROM WORKFLOW_STEP A ");
    sqlStat2.append(" WHERE A.RECORD_STATUS = 'A' ");
    sqlStat2.append(" AND FORM_SECTION_ID IS NOT NULL ");
    sqlStat2.append(" AND FORM_SECTION_ID <> '0|0' ");

    if (isConversionForm) {
      rs = stat.executeQuery(sqlStat2.toString());
    }
    String formSectStr;
    while (!Utility.isEmpty(rs) && rs.next()) {
      formSectStr = rs.getString("FORM_SECTION_ID");
      String string = formSectStr;
      String changeSecIDs = string.substring(0, string.indexOf("|"));
      String readSecIDs = string.substring(string.indexOf("|") + 1);
      String[] changeIDArr = changeSecIDs.split(",");
      String[] readIDArr = readSecIDs.split(",");

      for (int i = 0; i < changeIDArr.length; i++) {
        System.out.println("change:" + changeIDArr[i]);
        if (!"0".equals(changeIDArr[i])) {
          ID++;
          insertRecord(conn, changeIDArr[i], rs.getInt("ID"), ID, "C", rs.getInt("CREATOR_ID"), rs.getString("CREATE_DATE"), rs.getInt("UPDATER_ID"), rs
              .getString("UPDATE_DATE"));
        }
      }

      for (int i = 0; i < readIDArr.length; i++) {
        System.out.println("read:" + readIDArr[i]);
        if (!"0".equals(readIDArr[i])) {
          ID++;
          insertRecord(conn, readIDArr[i], rs.getInt("ID"), ID, "R", rs.getInt("CREATOR_ID"), rs.getString("CREATE_DATE"), rs.getInt("UPDATER_ID"), rs
              .getString("UPDATE_DATE"));
        }
      }
    }
    // recode the ID into Sys_Table_Key
    StringBuffer sqlStat4 = new StringBuffer();
    sqlStat4.append(" INSERT INTO SYS_TABLE_KEY (TABLE_NAME, TABLE_KEY_MAX) VALUES (?, ?)  ");

    java.sql.PreparedStatement stat4 = conn.prepareStatement(sqlStat4.toString());
    stat4.setString(1, "MTM_WF_STEP_FORM_RECORD");
    stat4.setInt(2, ID++);
    if (isConversionForm) {
      stat4.executeQuery();
    }
    stat4.close();
    return stat;
  }

  /**
   * @param conn
   * @param stat
   * @throws SQLException
   */
  private void convertFormSubmission(Connection conn) throws SQLException {
    ResultSet rs;
    Statement stat = conn.createStatement();
    StringBuffer sqlStat5;
    sqlStat5 = new StringBuffer();
    sqlStat5.append(" SELECT A.TRACK_ID, C.WORKFLOW_RECORD_ID, D.ID, D.FORM_RECORD_ID,D.TRACK_ID, D.WORKFLOW_RECORD_ID ");
    sqlStat5.append(" FROM MTM_WF_PROGRESS_SYSTEM_OBJECT A, WORKFLOW_PROGRESS B, WORKFLOW_STEP C, FORM_SUBMISSION D ");
    sqlStat5.append(" WHERE D.RECORD_STATUS = 'A' ");
    sqlStat5.append(" AND A.WORKFLOW_PROGRESS_ID = B.ID ");
    sqlStat5.append(" AND B.WORKFLOW_STEP_ID = C.ID ");
    sqlStat5.append(" AND A.OBJECT_ID = D.ID ");
    sqlStat5.append(" AND A.BEAN_CLASS_NAME = 'com.dcivision.form.bean.FormSubmission' ");
    sqlStat5.append(" AND D.TRACK_ID  IS  NULL ");
    // sqlStat5.append(" AND D.STATUS <> 'P' ");

    rs = stat.executeQuery(sqlStat5.toString());

    while (rs.next()) {
      updateFormSubmission(conn, rs.getInt("ID"), rs.getInt("WORKFLOW_RECORD_ID"), rs.getInt("TRACK_ID"));
    }

    rs.close();
  }

  /**
   * @param conn
   * @return
   */
  private boolean isConversionForm(Connection conn) {
    int count = SqlAccountUtility.getIDCountFromSql(conn, "MTM_WF_STEP_FORM_RECORD", "");
    return count > 0 ? true : false;
  }

  /**
   * @param conn
   * @param stat
   * @return
   * @throws SQLException
   */
  private int insertEveryoneRPermissonForNotExecuteAction(Connection conn) throws SQLException {
    PreparedStatement stat4;
    Statement stat = conn.createStatement();
    stat = conn.createStatement();
    ResultSet rs = stat.executeQuery(" SELECT COUNT(*) AS COUNT FROM MTM_WF_STEP_USER_ACTOR WHERE ACTOR_TYPE = 'Z' ");
    if (rs.next() && rs.getInt("COUNT") > 0) {
      isConversionEveryone = false;
    }

    // get the ID value
    int userActorID = 0;
    StringBuffer sqlStat5 = new StringBuffer();
    sqlStat5.append(" SELECT TABLE_KEY_MAX FROM SYS_TABLE_KEY WHERE TABLE_NAME = 'MTM_WF_STEP_USER_ACTOR'");

    rs = stat.executeQuery(sqlStat5.toString());

    if (rs.next()) {
      userActorID = rs.getInt("TABLE_KEY_MAX") + 1;
    }

    sqlStat5 = new StringBuffer();
    sqlStat5.append(" SELECT A.ID, A.ACTION_TYPE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
    sqlStat5.append(" FROM WORKFLOW_STEP A ");
    sqlStat5.append(" WHERE A.RECORD_STATUS = 'A' AND ACTION_TYPE <> 'E' "); // ACTION_TYPE_END
    // =
    // "E";

    if (isConversionEveryone) {
      rs = stat.executeQuery(sqlStat5.toString());
    }

    // if current Step not end step,insert everyone read permission on the table
    // MTM_WF_STEP_USER_ACTOR
    while (!Utility.isEmpty(rs) && rs.next()) {
      userActorID++;
      insertUserActor(conn, userActorID, rs.getInt("ID"), "E", rs.getInt("CREATOR_ID"), rs.getString("CREATE_DATE"), rs.getInt("UPDATER_ID"), rs
          .getString("UPDATE_DATE"));
    }

    // out.println("============= Update Sys_Table_Key Table
    // ==============<br>");
    // Update Sys_Table_Key
    sqlStat5 = new StringBuffer();
    sqlStat5.append(" SELECT * FROM SYS_TABLE_KEY WHERE TABLE_NAME = 'MTM_WF_STEP_USER_ACTOR' ");
    rs = stat.executeQuery(sqlStat5.toString());

    sqlStat5 = new StringBuffer();
    if (rs.next()) {
      sqlStat5.append(" UPDATE SYS_TABLE_KEY SET TABLE_KEY_MAX = ?  WHERE TABLE_NAME = ?  ");
    } else {
      sqlStat5.append(" INSERT INTO SYS_TABLE_KEY (TABLE_KEY_MAX, WHERE TABLE_NAME) VALUES(?, ?)  ");
    }

    stat4 = conn.prepareStatement(sqlStat5.toString());
    stat4.setInt(1, userActorID++);
    stat4.setString(2, "MTM_WF_STEP_USER_ACTOR");
    if (isConversionEveryone) {
      stat4.executeQuery();
    }
    stat4.close();
    return userActorID;
  }

}
